package com.gem.controller;


import com.gem.domain.*;
import com.gem.service.CareerService;
import com.gem.service.ClassInfoService;
import com.gem.service.ScoreService;
import com.gem.service.StudentService;
import com.gem.util.ExcelUtil;
import com.gem.util.MessageUtil;
import com.gem.util.PageUtil;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static com.gem.util.ExcelUtil.setHSSFValidation;

@Controller
@RequestMapping("/career")
@SuppressWarnings("all")
public class ClassInfoController {

    @Autowired
    private ClassInfoService service;

    @Autowired
    private ScoreService scoreService;

    @Autowired
    private StudentService studentService;

    @Autowired
    private CareerService careerService;

    //查看班级信息
    @RequestMapping("/findAll")
    public String findAll(Model model,
                          @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                          @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            PageInfo<ClassInfo> pageBean = service.findAll(currentPage, pageSize);
            System.out.println("班级查询结果是:" + pageBean);
            if (pageBean != null && pageBean.getList().size() > 0) {
                model.addAttribute("pageBean", pageBean);
                List<Integer> pageValue = PageUtil.getPageValue(pageBean.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
            } else {
                model.addAttribute("info", "暂无信息记录");
            }
            model.addAttribute("pageSize", pageSize);
            return "career/class";
        } catch (Exception e) {
            e.printStackTrace();
        }
        MessageUtil.returnInfoPage("3", "/career/findAll", "服务器异常，请联系管理员哦！", model);
        return "message";
    }

    //查找班级
    @RequestMapping("/searchClass")
    public String searchClass(Model model, ClassInfo classinfo,
                              @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                              @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            Date classDate = classinfo.getStartTime();
            String classType = classinfo.getSName();
            String classTeacher = classinfo.getTName();
            String classHeadTeacher = classinfo.getMName();
            PageInfo<ClassInfo> pageBean = service.searchClassInfo(classinfo, currentPage, pageSize);
            System.out.println("班级查询结果是:" + pageBean);
            if (pageBean != null && pageBean.getList().size() > 0) {
                model.addAttribute("pageBean", pageBean);
                List<Integer> pageValue = PageUtil.getPageValue(pageBean.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);//当前页的页数
                model.addAttribute("searchUrl", "/career/searchClass");
                model.addAttribute("classDate", classDate);
                model.addAttribute("classType", classType);
                model.addAttribute("classHeadTeacher", classHeadTeacher);
                model.addAttribute("classTeacher", classTeacher);
            } else {
                model.addAttribute("info", "暂无信息记录");
            }
            model.addAttribute("pageSize", pageSize);
            return "career/class";
        } catch (Exception e) {
            e.printStackTrace();
        }
        MessageUtil.returnInfoPage("3", "/career/searchClass", "服务器异常，请联系管理员哦！", model);
        return "message";
    }




    //导出excel
    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response, int[] classIds) throws Exception {
        if (classIds != null && classIds.length > 0) {
            OutputStream outputStream = null;
            //创建excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);
            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);
            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateStyle(workbook);
            //创建行对象
            HSSFRow row = null;

            for (int i = 0; i < classIds.length; i++) {
                String className = service.findById(classIds[(int)i]).getCId();
                //我的院校
                //创建sheet对象
                HSSFSheet sheet = workbook.createSheet("成绩信息");

                // 将第一行的三个单元格给合并,模板固定
                //注意 java的参数是从0开始 即表示excel中的一行或者第一列
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
                row = sheet.createRow(0);
                HSSFCell beginCell0 = row.createCell(0);
                beginCell0.setCellValue(className + "----成绩信息表");
                //cellStyle为水平居中字体加粗单元格样式
                beginCell0.setCellStyle(cellStyle);

                HSSFRow headRow = sheet.createRow(2);
                headRow.createCell(0).setCellValue("学生姓名");
                headRow.createCell(1).setCellValue("平均成绩");
                headRow.createCell(2).setCellValue("笔试成绩");
                headRow.createCell(3).setCellValue("面试成绩");
                headRow.createCell(4).setCellValue("实战项目名");
                headRow.createCell(5).setCellValue("线上成绩(0不通过，1通过)");
                headRow.createCell(6).setCellValue("线下成绩（0不通过，1通过)");
                headRow.createCell(7).setCellValue("简历（0不通过，1通过）");
                headRow.createCell(8).setCellValue("综测结果最终评价(ABCD)");
                headRow.createCell(9).setCellValue("备注");
                headRow.setHeightInPoints(25); // 设置行的高度
                for (int l = 0; l < 10; l++) {
                    headRow.getCell(l).setCellStyle(cellStyle2);
                }


                //创建水平垂直居中加粗的cellStyle单元格样式,给数据试用
                HSSFCellStyle rowStyle = ExcelUtil.getCenterStyle(workbook);
                //调用service方法查询所有数据集合
                List<Score> scoreList = scoreService.showScore(classIds[i]);
                //优化 降低冗余
                HSSFCell cell = null;
                Score score = null;
                //设置所有列列宽
                for (int j = 0; j < 10; j++) {
                    sheet.setColumnWidth(j, 20 * 300);
                } // 设置列的宽度


                for (int k = 0; k < scoreList.size(); k++) {
                    row = sheet.createRow(k + 3);
                    score = scoreList.get(k);
                    //第一列 学生姓名
                    cell = row.createCell(0);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(score.getSname());

                    //第二列 平均成绩
                    cell = row.createCell(1);
                    cell.setCellStyle(rowStyle);
                    if ( score.getAverGrade() == null || score.getAverGrade() == -1 ) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(score.getAverGrade());
                    }

                    //第三列 笔试成绩
                    cell = row.createCell(2);
                    cell.setCellStyle(rowStyle);
                    if (score.getWritGrade() == null || score.getWritGrade() == -1 ) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(score.getWritGrade());
                    }

                    //第四列 面试成绩
                    cell = row.createCell(3);
                    cell.setCellStyle(rowStyle);
                    if ( score.getViewGrade() == null || score.getViewGrade() == -1 ) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(score.getViewGrade());
                    }

                    //第五列 实战项目名
                    cell = row.createCell(4);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(score.getProject());

                    //第六列 线上成绩(0不通过，1通过,-1没写)
                    cell = row.createCell(5);
                    cell.setCellStyle(rowStyle);
                    if ( score.getUpGrade() == null || score.getUpGrade() == -1 ) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue((score.getUpGrade() == 0)?"不通过":"通过");
                    }

                    //第七列 线下成绩（0不通过，1通过，-1没写)
                    cell = row.createCell(6);
                    cell.setCellStyle(rowStyle);
                    if (score.getDownGrade()==null || score.getDownGrade() == -1) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue((score.getDownGrade() == 0)?"不通过":"通过");
                    }

                    //第八列 简历（0不通过，1通过,-1没写)
                    cell = row.createCell(7);
                    cell.setCellStyle(rowStyle);
                    if ( score.getResume() == null || score.getResume() == -1) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue((score.getResume() == 0)?"不通过":"通过");
                    }

                    //第九列 综测结果最终评价(ABCD)
                    cell = row.createCell(8);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(score.getResult());

                    //第九列 备注
                    cell = row.createCell(9);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(score.getRemark());
                }
                //学生详情
                //创建sheet对象
                HSSFSheet sheet1 = workbook.createSheet("学生详情");
                sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
                row = sheet1.createRow(0);
                HSSFCell beginCell_10 = row.createCell(0);
                beginCell_10.setCellValue(className + "----学生详情表");
                //cellStyle为水平居中字体加粗单元格样式
                beginCell_10.setCellStyle(cellStyle);

                /*次数写表头,根据各个页面的进行修改*/
                HSSFRow headRow1 = sheet1.createRow(2);
                headRow1.createCell(0).setCellValue("学生id");
                headRow1.createCell(1).setCellValue("学生姓名");
                headRow1.createCell(2).setCellValue("性别：0未填，1男，2女");
                headRow1.createCell(3).setCellValue("学生学校");
                headRow1.createCell(4).setCellValue("学生专业");
                headRow1.createCell(5).setCellValue("外语水平");
                headRow1.createCell(6).setCellValue("证书，没有填无");
                headRow1.createCell(7).setCellValue("学历");
                headRow1.createCell(8).setCellValue("毕业时间");
                headRow1.createCell(9).setCellValue("电话");
                headRow1.createCell(10).setCellValue("qq");
                headRow1.createCell(11).setCellValue("身份证");
                headRow1.createCell(12).setCellValue("家长电话");
                headRow1.createCell(13).setCellValue("家庭地址");
                headRow1.createCell(14).setCellValue("邮箱");
                headRow1.createCell(15).setCellValue("招生老师");
                headRow1.createCell(16).setCellValue("学生状态（-1毕业，1在读）");
                headRow1.createCell(17).setCellValue("备注");
                headRow1.setHeightInPoints(25); // 设置行的高度
                for (int si = 0; si < 10; si++) {
                    headRow1.getCell(si).setCellStyle(cellStyle2);
                }
                //设置所有列列宽
                for (int si = 0; si < 10; si++) {
                    sheet1.setColumnWidth(si, 20 * 300);
                } // 设置列的宽度

                List<Student> studentList = studentService.selectAllStuByClassService(classIds[i]);
                Student student = null;

                for (int si = 0; si < studentList.size(); si++) {
                    row = sheet1.createRow(si + 3);
                    student = studentList.get(si);

                    cell = row.createCell(0);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSId());

                    cell = row.createCell(1);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSName());

                    cell = row.createCell(2);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSGender());

                    cell = row.createCell(3);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSSchoolName());

                    cell = row.createCell(4);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSMajor());

                    cell = row.createCell(5);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSCet());

                    cell = row.createCell(6);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSCertificate());

                    cell = row.createCell(7);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSEducation());

                    cell = row.createCell(8);
                    cell.setCellStyle(cellStyleDate);
                    cell.setCellValue(student.getSGradtime());

                    cell = row.createCell(9);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSSelfTel());

                    cell = row.createCell(10);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSQq());

                    cell = row.createCell(11);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSIdCard());

                    cell = row.createCell(12);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSParentTel());

                    cell = row.createCell(13);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSParentTel());

                    cell = row.createCell(14);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSAddress());

                    cell = row.createCell(15);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSEmail());

                    cell = row.createCell(16);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSRecruiter());

                    cell = row.createCell(17);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(student.getSRemark());

                }




                //就业情况
                //创建sheet对象
                HSSFSheet sheet2 = workbook.createSheet("就业情况");
                sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
                row = sheet2.createRow(0);
                HSSFCell beginCell_20 = row.createCell(0);
                beginCell_20.setCellValue(className + "----就业情况表");
                //cellStyle为水平居中字体加粗单元格样式
                beginCell_20.setCellStyle(cellStyle);

                /*次数写表头,根据各个页面的进行修改*/
                HSSFRow headRow2 = sheet2.createRow(2);
                headRow2.createCell(0).setCellValue("学生id");
                headRow2.createCell(1).setCellValue("学生姓名");
                headRow2.createCell(2).setCellValue("是否就业");
                headRow2.createCell(3).setCellValue("就业城市");
                headRow2.createCell(4).setCellValue("就业公司");
                headRow2.createCell(5).setCellValue("职位");
                headRow2.createCell(6).setCellValue("实习工资");
                headRow2.createCell(7).setCellValue("正式工资");
                headRow2.createCell(8).setCellValue("推荐老师");
                headRow2.createCell(9).setCellValue("五险一金");
                headRow2.createCell(10).setCellValue("备注");
                headRow2.setHeightInPoints(25); // 设置行的高度
                for (int ci = 0; ci < 11; ci++) {
                    headRow2.getCell(ci).setCellStyle(cellStyle2);
                }
                //设置所有列列宽
                for (int ci = 0; ci < 11; ci++) {
                    sheet2.setColumnWidth(ci, 25 * 256);
                } // 设置列的宽度

                List<StuCareer> careerList = careerService.findByCid(classIds[(int)i]);
                StuCareer stuCareer = null;

                for (int ci = 0; ci < careerList.size(); ci++) {
                    row = sheet2.createRow(ci + 3);
                    stuCareer = careerList.get(ci);
                    //第一列 姓名
                    cell = row.createCell(0);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getSid());

                    //第二列 性别
                    cell = row.createCell(1);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerStuName());

                    //第三列 职务
                    cell = row.createCell(2);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerIsCareer());
                    //第四列 渠道专员
                    cell = row.createCell(3);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerCity());

                    //第五列 联系方式
                    cell = row.createCell(4);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerCompany());

                    //第六列 职务
                    cell = row.createCell(5);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerJob());

                    //第七列 备注
                    cell = row.createCell(6);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerInternship());

                    //第八列 创建时间
                    cell = row.createCell(7);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerSalary());//赋值

                    //第九列 修改时间
                    cell = row.createCell(8);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerTeacher());//赋值

                    cell = row.createCell(9);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerWelfare());//赋值

                    cell = row.createCell(10);
                    cell.setCellStyle(rowStyle);
                    cell.setCellValue(stuCareer.getCareerRemarks());//赋值
                }


                outputStream = response.getOutputStream();
                response.reset();
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-disposition", "attachment;filename=ClassInfo.xls");

                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            }

            //end for
        }
        //end if
    }

}
